﻿#include "excelthread.h"
#include "excelengine.h"
#include "loadingwidget.h"
#include <QSemaphore>

#include <QMessageBox>
#include <QDebug>
#include <QFileInfo>
#include <QTableView>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>

//#include <>

#include <QDateTime>

ExcelThread::ExcelThread(QString filename, ExcelOperatorType type, QObject *parent) :
    QThread(parent),widgetLoad(nullptr),m_ItemIndex(-1),
	optType(type)
{
	if(optType == ExcelOperatorType::NoOperator)
		optType = ExcelOperatorType::ReadData;

	qRegisterMetaType<QMap<int, QString>>("QMap<int, QString>");
    qRegisterMetaType<QPair<QMap<int,QString>,QMap<int,QString>>>("QPair<QMap<int,QString>,QMap<int,QString>>");
    qRegisterMetaType<excelHandle>("excelHandle");

	m_filename = filename;
}

void ExcelThread::run()
{
qDebug()<<QStringLiteral("线程里写入")<<m_filename<<optType;

    sem_i = 1;
	{
		switch(optType){
        case ExcelOperatorType::ReadData:{
            /*QSqlDatabase*/
//            QSemaphore sem(1);
//            sem.acquire();
            excelHandle m_exlRecord(m_filename);
            m_exlRecord.setGroupNum(QString::number(groupNum));
            m_exlRecord.setItemIndex(QString::number(m_ItemIndex));

            QFileInfo fileInfo(m_filename);
            QSqlDatabase db = QSqlDatabase::addDatabase("QODBC",fileInfo.baseName());
            QString dbn = QString("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
                                  "FIL={Excel xlsx};HDR=Yes;IMEX=1;Readonly=%1;DriverId=790;Dbq=%2;"
                                  "DefaultDir=e:\\");

            dbn = dbn.arg(QString::number(0),m_filename);
            db.setDatabaseName(dbn);
            if(!db.open()){
                qDebug()<<__LINE__<<("openDb error:%s",db.lastError())<<fileInfo.baseName();
            }

//            QMutex mutex;
            QSqlQuery query(db);
            for(int i=1;i<5;i++){
                mutex.lock();
                QString tableName = QStringLiteral("[第")+
                        QString("%1").arg(QString::number(i))+
                        QStringLiteral("餐投喂标准$]");
//                m_exlRecord.insertTable(tableName);
                QString strlisttbl(tableName);
                strlisttbl.replace("[","");
                strlisttbl.replace("$]","");
                m_exlRecord.insertTable(strlisttbl);
                query.exec(QString("select * from %1").arg(tableName));

                //! [1] get value of start search column.
                QSqlQueryModel querymodel;
                querymodel.setQuery(query);
                //search match date
                //num of row to start is 0,num of column to start is 3->the fouth field
                int column_date = -1;
                int row_valuestart = -1;
                QDateTime startDate(QDate(1900, 1, 1),QTime(0,0,0));
                QDateTime endDate = QDateTime::currentDateTime();
                qint64 realedays = startDate.daysTo(endDate) +2;//the reale day.

                for(int i_date = 3;i_date<querymodel.columnCount();i_date++){
                    QVariant variant;
                    if(querymodel.record(0).value(i_date).canConvert<QDateTime>()){
                        variant = querymodel.record(0).value(i_date).toDateTime();
                        if(variant.toDateTime().toString("MMdd").contains(QString::number(realedays))){
                            column_date = i_date;
                            break;
                        }
                    }
                    else if(querymodel.record(0).value(i_date).canConvert<QString>())
                    {
                        if(querymodel.record(0).value(i_date).toString().contains(QString::number(realedays))){
                            column_date = i_date;
                            break;
                        }
                    }
                }
                //! [1]

                //! [2] get box num
                QMap<int,QPair<QString/*boxNum*/,QString/*PutValue*/>> mapboxAndPutvalue;
                for(int i_box = 0;i_box<querymodel.rowCount();i_box++){
                    QPair<QString,QString> pair;
                    if(querymodel.record(i_box).value(2).toInt()){
                        if(row_valuestart < 0)
                            row_valuestart = i_box;
                        m_mapbox.insert(i_box,querymodel.record(i_box).value(2).toString());
                        pair.first = querymodel.record(i_box).value(2).toString();
                        if(pair.first.toInt() == 23)
                            qDebug()<<"value of problem.";
                        QString secondvalue = querymodel.record(i_box).value(column_date).toString();
                        if(secondvalue.contains(".")){
                            QStringList list = secondvalue.split(".");
                            if(list.count()>1)
                                secondvalue = list.first()+"."+list.last().left(2);
                        }
                        pair.second = secondvalue;//querymodel.record(i_box).value(column_date).toString().truncate();
                        mapboxAndPutvalue.insert(i_box,pair);
                    }
                }
                m_exlRecord.InsertTBBoxAndPutValue(m_exlRecord.getTableIndex(strlisttbl),mapboxAndPutvalue);
                //! [2]

                //! [3] get put value
                for(int i_reale = row_valuestart;i_reale<querymodel.rowCount();i_reale++){
                    if(querymodel.record(i_reale).value(column_date).toInt() && querymodel.record(i_reale).value(column_date).toInt()<1000){
                        m_mapvalue.insert(querymodel.record(i_reale).value(2).toInt(),
                                          querymodel.record(i_reale).value(column_date).toString());
                    }
                }
                qDebug()<<"getdata from excel:"<<m_mapvalue<<endl<<endl<<m_mapbox<<endl<<endl<<row_valuestart<<column_date<<querymodel.record(row_valuestart).value(2)
                       <<querymodel.record(row_valuestart).value(column_date);

                //! [3]
                if(m_exlRecord.getTableIndex(strlisttbl) != -1){
                    m_exlRecord.InsertBox(m_exlRecord.getTableIndex(strlisttbl),m_mapbox);
                    m_exlRecord.InsertPutValue(m_exlRecord.getTableIndex(strlisttbl),m_mapvalue);
                }
                mutex.unlock();
            }
            db.close();
//            sem.release();

            emit sig_finishedWithExlRecord(m_exlRecord);
        }
            break;
        case ExcelOperatorType::WriteData:{//exlRecordHandle
            QFileInfo fileInfo(m_filename);
            QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","w_" + fileInfo.baseName());
            db.setDatabaseName(m_filename);

            if(!db.open()){
                qDebug()<<__LINE__<<("openDb error:%s",db.lastError())<<fileInfo.baseName();
            }

            QSqlQuery query(db);
            foreach(QString tableName,exlRecordHandle.getTables())
            {
                mutex.lock();
                QStringList m_listField = QStringList()<<"id INTEGER PRIMARY KEY AUTOINCREMENT "<<"boxnum varchar UNIQUE"<<"boxvalue varchar ";

                QString str_fieldTbl;
                str_fieldTbl = m_listField.join(",");
                str_fieldTbl.insert(0,"( ");
                str_fieldTbl.insert(str_fieldTbl.length()," )");

                query.exec(QString("create table %1 %2").arg(tableName,str_fieldTbl));
                //insert into table() values();
                db.transaction();

//                QHash<int,QHash<int,QString>>::const_iterator it_putvalue = exlRecordHandle.getPutValueMap().constFind(exlRecordHandle.getTableIndex(tableName));
                QMap<int,QMap<int,QPair<QString,QString>>>::const_iterator it_putvalue = exlRecordHandle.getTBBoxAndPutValue().constFind(exlRecordHandle.getTableIndex(tableName));
                if(it_putvalue != exlRecordHandle.getTBBoxAndPutValue().constEnd())
                {                    
                    QMapIterator<int,QPair<QString,QString>> itor(it_putvalue.value());
                    while (itor.hasNext())
                    {
                        itor.next();
                        bool m_insert = query.exec(QString("insert into %1 (boxnum,boxvalue) values(%2,%3)").
                                                   arg(tableName,
                                                       (itor.value().first),itor.value().second));
//                            qDebug()<<"it_putvalue:"<<query.lastError()<<query.lastQuery()<<exlRecordHandle.getTableIndex(tableName)<<itor.key()<<itor.value()<<exlRecordHandle.getGroupNum()<<exlRecordHandle.getItemIndex();

                        if(!query.numRowsAffected() || !m_insert){
                            query.exec(QString("update %1 set boxvalue='%2' where boxnum='%3'").arg(
                                           tableName,itor.value().second,(itor.value().first)));
                        }
                    }
                }
                db.commit();
                mutex.unlock();
            }
            db.close();
            emit finishedWriteToSql(exlRecordHandle.getGroupNum().toInt(),exlRecordHandle.getItemIndex().toInt());
        }
            break;
		}
	}
    sem_i++;
}

void ExcelThread::setWriteDate(QMap<int, QString> mMap)
{
	m_mapWrite = mMap;
}

void ExcelThread::setGroup(int num)
{
	groupNum=num;
}

void ExcelThread::finishedReadFromXls()
{
	emit this->finishedReadData(groupNum,m_map);
}
